package com.luobd.db.impl;

import com.luobd.db.Column;
import com.luobd.db.MetadataFetcher;
import com.luobd.db.Table;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OracleMetadataFetcher implements MetadataFetcher {
    private final Connection connection;
    private final String schema;

    public OracleMetadataFetcher(Connection connection, String schema) {
        this.connection = connection;
        this.schema = schema; // Oracle的schema通常等于用户名
    }

    @Override
    public List<Table> getTables() throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        List<Table> tables = new ArrayList<>();

        try (ResultSet tablesRs = metaData.getTables(null, schema, "%", new String[]{"TABLE"})) {
            while (tablesRs.next()) {
                Table table = new Table();
                String tableName = tablesRs.getString("TABLE_NAME");
                table.setName(tableName);
                table.setComment(getOracleTableComment(tableName));

                List<String> primaryKeys = getPrimaryKeys(tableName);
                table.setPrimaryKeys(primaryKeys);

                List<Column> columns = getColumns(tableName, primaryKeys);
                table.setColumns(columns);

                tables.add(table);
            }
        }
        return tables;
    }

    // Oracle需要单独获取注释
    private String getOracleTableComment(String tableName) throws SQLException {
        String sql = "SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = ? AND TABLE_NAME = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, schema);
            ps.setString(2, tableName);
            ResultSet rs = ps.executeQuery();
            return rs.next() ? rs.getString(1) : "";
        }
    }

    private List<String> getPrimaryKeys(String tableName) throws SQLException {
        List<String> keys = new ArrayList<>();
        try (ResultSet rs = connection.getMetaData().getPrimaryKeys(null, schema, tableName)) {
            while (rs.next()) {
                keys.add(rs.getString("COLUMN_NAME"));
            }
        }
        return keys;
    }

    private List<Column> getColumns(String tableName, List<String> primaryKeys) throws SQLException {
        List<Column> columns = new ArrayList<>();
        String sql = "SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT, COMMENTS "
                + "FROM ALL_TAB_COLUMNS c "
                + "LEFT JOIN ALL_COL_COMMENTS cc ON (c.OWNER=cc.OWNER AND c.TABLE_NAME=cc.TABLE_NAME AND c.COLUMN_NAME=cc.COLUMN_NAME) "
                + "WHERE c.OWNER = ? AND c.TABLE_NAME = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, schema);
            ps.setString(2, tableName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Column col = new Column();
                col.setName(rs.getString("COLUMN_NAME"));
                col.setType(rs.getString("DATA_TYPE"));
                col.setSize(rs.getInt("DATA_LENGTH"));
                col.setNullable("Y".equals(rs.getString("NULLABLE")));
                col.setPrimaryKey(primaryKeys.contains(col.getName()));
                col.setComment(rs.getString("COMMENTS"));
                columns.add(col);
            }
        }
        return columns;
    }
}